#!/usr/bin/env node

const Database = require('better-sqlite3');
const db = new Database('./data/delivery-management.db');

console.log('🔄 重新组织实例表结构...\n');

try {
  // 专业前缀映射
  const specialtyPrefixes = {
    '配管专业': 'piping',
    '电气专业': 'electrical',
    '暖通专业': 'hvac',
    '通信专业': 'communication',
    '仪表专业': 'instrumentation'
  };

  console.log('1. 分析现有的对象类映射...');
  
  // 获取所有对象类的映射信息
  const objectClasses = db.prepare(`
    SELECT DISTINCT
      dtm.classification_name,
      dtm.specialty,
      dtm.english_name,
      dtm.classification_code
    FROM device_type_mappings dtm
    WHERE dtm.classification_name IS NOT NULL 
      AND dtm.classification_name != ''
      AND dtm.english_name IS NOT NULL
      AND dtm.english_name != ''
    ORDER BY dtm.specialty, dtm.classification_name
  `).all();

  console.log(`找到 ${objectClasses.length} 个对象类需要创建实例表`);

  console.log('\n2. 生成标准实例表名称...');
  
  const standardTables = new Map();
  const classificationToTable = new Map();
  
  objectClasses.forEach(cls => {
    const specialtyPrefix = specialtyPrefixes[cls.specialty];
    if (specialtyPrefix) {
      const tableName = `device_instances_${specialtyPrefix}_${cls.english_name}`;
      standardTables.set(tableName, {
        classification_name: cls.classification_name,
        specialty: cls.specialty,
        english_name: cls.english_name
      });
      
      // 建立分类代码到表名的映射
      if (!classificationToTable.has(cls.classification_name)) {
        classificationToTable.set(cls.classification_name, tableName);
      }
    }
  });

  console.log(`需要创建 ${standardTables.size} 个标准实例表`);

  console.log('\n3. 检查现有表和数据...');
  
  // 获取现有的实例表
  const existingTables = db.prepare(`
    SELECT name FROM sqlite_master 
    WHERE type='table' AND name LIKE 'device_instances_%'
    ORDER BY name
  `).all().map(t => t.name);

  console.log(`现有实例表: ${existingTables.length} 个`);

  // 分析哪些表需要创建，哪些需要合并数据
  const tablesToCreate = [];
  const tablesToMigrate = [];
  
  standardTables.forEach((info, tableName) => {
    if (!existingTables.includes(tableName)) {
      tablesToCreate.push(tableName);
    }
  });

  // 找出需要迁移数据的旧表
  existingTables.forEach(tableName => {
    if (!standardTables.has(tableName)) {
      tablesToMigrate.push(tableName);
    }
  });

  console.log(`需要创建的新表: ${tablesToCreate.length} 个`);
  console.log(`需要迁移数据的旧表: ${tablesToMigrate.length} 个`);

  console.log('\n4. 创建标准实例表...');
  
  let createdCount = 0;
  
  tablesToCreate.forEach(tableName => {
    try {
      const createTableSQL = `
        CREATE TABLE [${tableName}] (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          device_type_code TEXT NOT NULL,
          instance_number TEXT NOT NULL,
          object_number TEXT NOT NULL,
          object_description TEXT,
          naming_record_id INTEGER,
          position_record_id INTEGER,
          base_params_record_id INTEGER,
          created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
          updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
          UNIQUE(device_type_code, instance_number)
        )
      `;
      
      db.prepare(createTableSQL).run();
      console.log(`  ✓ 创建: ${tableName}`);
      createdCount++;
      
    } catch (error) {
      console.log(`  ✗ 创建失败 ${tableName}: ${error.message}`);
    }
  });

  console.log(`成功创建 ${createdCount} 个新表`);

  console.log('\n5. 数据迁移规划...');
  
  // 分析需要迁移的数据
  const migrationPlan = [];
  
  tablesToMigrate.forEach(oldTableName => {
    try {
      // 检查表是否有数据
      const count = db.prepare(`SELECT COUNT(*) as count FROM [${oldTableName}]`).get();
      
      if (count.count > 0) {
        // 尝试通过表名推断目标表
        let targetTable = null;
        
        // 检查是否是传统格式的表名
        if (oldTableName.match(/^device_instances_[^_]+_\d+$/)) {
          // 格式：device_instances_配管_0001
          const deviceId = oldTableName.replace('device_instances_', '');
          
          // 查找对应的设备信息
          const device = db.prepare(`
            SELECT d.*, dtm.english_name, dtm.specialty 
            FROM devices d
            LEFT JOIN device_type_mappings dtm ON d.classification_code = dtm.classification_code
            WHERE d.device_id = ?
          `).get(deviceId);
          
          if (device && device.english_name && specialtyPrefixes[device.specialty]) {
            targetTable = `device_instances_${specialtyPrefixes[device.specialty]}_${device.english_name}`;
          }
        }
        
        if (targetTable && standardTables.has(targetTable)) {
          migrationPlan.push({
            source: oldTableName,
            target: targetTable,
            recordCount: count.count
          });
        } else {
          console.log(`  ⚠️  无法确定 ${oldTableName} 的目标表 (${count.count} 条记录)`);
        }
      }
    } catch (error) {
      console.log(`  ✗ 检查表 ${oldTableName} 失败: ${error.message}`);
    }
  });

  console.log(`\n找到 ${migrationPlan.length} 个表需要数据迁移:`);
  migrationPlan.forEach(plan => {
    console.log(`  ${plan.source} → ${plan.target} (${plan.recordCount} 条记录)`);
  });

  if (migrationPlan.length > 0) {
    console.log('\n⚠️  数据迁移需要手动确认。使用以下命令执行迁移:');
    console.log('   node scripts/execute-migration.js');
  }

  console.log('\n6. 验证最终结果...');
  
  const finalTables = db.prepare(`
    SELECT name FROM sqlite_master 
    WHERE type='table' AND name LIKE 'device_instances_%'
    ORDER BY name
  `).all();

  const standardTableCount = [...finalTables].filter(t => 
    standardTables.has(t.name)
  ).length;

  console.log(`\n📊 最终统计:`);
  console.log(`- 总实例表数: ${finalTables.length}`);
  console.log(`- 标准格式表数: ${standardTableCount}`);
  console.log(`- 需要清理的表数: ${finalTables.length - standardTableCount}`);
  
  // 按专业统计标准表
  const specialtyTableStats = {};
  standardTables.forEach((info, tableName) => {
    const specialty = info.specialty;
    if (!specialtyTableStats[specialty]) {
      specialtyTableStats[specialty] = 0;
    }
    specialtyTableStats[specialty]++;
  });

  console.log(`\n🏢 标准表按专业分布:`);
  Object.entries(specialtyTableStats).forEach(([specialty, count]) => {
    console.log(`  ${specialty}: ${count}个表`);
  });

  console.log('\n✅ 实例表结构重组完成!');
  console.log('💡 下一步: 执行数据迁移并清理冗余表');

} catch (error) {
  console.error('❌ 重组过程中发生错误:', error.message);
  console.error(error.stack);
} finally {
  db.close();
}